from datetime import datetime, timedelta
import pymongo
from bson import ObjectId
from request_model import AddressSaveModel,EvaluateModel,TuanCreateModel
from concurrent.futures import ThreadPoolExecutor
import redis
import logging
from math import ceil

class queryService:
    def __init__(self):
        self.client = pymongo.MongoClient(host='139.196.92.103', port=27017, username='root', password='wby6225104')
        self.db = self.client["jx3_pintuan"]
        # 初始化 Redis 连接
        self.redis_client = redis.Redis(host='localhost', port=6379, db=0)
        self.BATCH_SIZE = 20000  # 每批次处理的数据量
        self.MAX_WORKERS = 4     # 线程池大小

    ## 别名审核拒绝
    def fail_audit(self, id: str):
        result = self.db['pending_alias'].update_one({"_id": ObjectId(id)}, {"$set": {"status": 0}})
        if result.acknowledged:
            # 查询外观表中的别名
            pending_alias = self.db['pending_alias'].find_one({"_id": ObjectId(id)})
            if pending_alias:
                waiguan_id = pending_alias['waiguanId']
                alias_content = pending_alias['content']
                
                # 调试输出
                print(f"Deleting alias with content: {alias_content} from waiguanId: {waiguan_id}")
                
                # 删除外观表中的别名
                delete_result = self.db['jx3_all_data22'].update_one(
                    {"_id": ObjectId(waiguan_id)}, 
                    {"$pull": {
                        "pendingAlias": {
                            "content": alias_content
                        }
                    }}
                )
                
                # 检查删除结果
                if delete_result.modified_count > 0:
                    print("Alias successfully deleted.")
                else:
                    print("Failed to delete alias.")
                
                return True
        return False

    ## 别名审核通过 : status: 0 未通过, 1 通过
    def pass_audit(self, id: str):
        # 更新 pending_alias 集合中的 status
        result = self.db['pending_alias'].update_one({"_id": ObjectId(id)}, {"$set": {"status": 1}})
        if result.acknowledged:
            # 获取待审核的别名内容
            pending_alias = self.db['pending_alias'].find_one({"_id": ObjectId(id)})
            if pending_alias:
                waiguan_id = pending_alias['waiguanId']
                alias_content = pending_alias['content']
                
                # 更新 jx3_all_data22 集合中的 pendingAlias
                data_updated = self.db['jx3_all_data22'].update_one(
                    {"_id": ObjectId(waiguan_id), "pendingAlias.content": alias_content},
                    {"$set": {"pendingAlias.$.status": 1}}
                )
                
                if data_updated.acknowledged:
                    return True
                else:
                    return False
        return False

    ## 获取别名审核列表
    def getAliasAuditList(self, userId:str, page:int, limit:int):
        ## 审核用户id白名单
        audit_user_ids = ['oJ1de5EwkYm4JAvjKuJtF2mibvOU', 'oJ1de5K9cE1eXMGXbzfqpPggUS4A','oJ1de5LcZWmdy3NtvP5vjI9bxPmk']
        if userId not in audit_user_ids:
            return None
        result = self.db['pending_alias'].find(
            {
                "status":0
            },
            {
                "_id":1,
                "content":1,
                "waiguanId":1,
            }
        ).skip((page - 1) * limit).limit(limit)
        if result is None:
            return None
        else:
            # 将游标转换为列表并获取所需数据
            result_list = [
                {
                "id": str(item['_id']),
                "content": item['content'],
                "waiguanId": item['waiguanId'],
                "waiguanInfo": {**waiguan, '_id': str(waiguan['_id'])}
            } for item in result if (waiguan := self.db['jx3_all_data22'].find_one({"_id": ObjectId(item['waiguanId'])}))
            ]
            return result_list

        
    ## 获取价格走势
    def getPriceHistory(self, itemId: str, itemType: str, days: int):
        try:
            # 获取当前时间
            end_date = datetime.now()
            # 计算开始时间
            start_date = end_date - timedelta(days=days)
            
            # 使用聚合管道查询
            pipeline = [
                # 1. 匹配条件
                {
                    "$match": {
                        "attrs.item_index": int(itemId),
                        "attrs.item_type": int(itemType),
                        "updateTime": {
                            "$gte": start_date.strftime('%Y-%m-%d %H:%M:%S'),
                            "$lte": end_date.strftime('%Y-%m-%d %H:%M:%S')
                        }
                    }
                },
                # 2. 按日期分组，取每天最低价格
                {
                    "$group": {
                        "_id": {
                            "$substr": ["$updateTime", 0, 10]  # 按天分组
                        },
                        "min_price": {
                            "$min": "$single_unit_price"  # 取最低价格
                        }
                    }
                },
                # 3. 按日期排序
                {
                    "$sort": {
                        "_id": 1
                    }
                }
            ]
            
            result = self.db['jx3_trade_data'].aggregate(pipeline)
            
            # 处理数据格式
            dates = []
            prices = []
            for doc in result:
                # 格式化日期为 MM-DD 格式
                date_str = datetime.strptime(doc['_id'], '%Y-%m-%d').strftime('%m-%d')
                dates.append(date_str)
                prices.append(doc['min_price']/100)
            
            return {
                "dates": dates,
                "prices": prices
            }
            
        except Exception as e:
            print(f"获取价格历史失败: {str(e)}")
            return None

    ## 获取关注状态
    def is_followd(self, itemId, userId):
        result = self.db['toggle_like_waiguan'].find_one({"userId": userId,"waiguanId":{
            "$elemMatch":{
                "$eq": itemId
            }
        }
        })
        if result is not None:
            return True
        else:
            return False
   
    ## 关注外观
    def toggle_like_waiguan(self, waiguanId, userId):
        # 查找用户是否已经有关注记录
        result = self.db['toggle_like_waiguan'].find_one({"userId": userId})
        
        # 如果不存在，则创建新记录并添加外观id
        if result is None:
            return self.db['toggle_like_waiguan'].insert_one({
                "userId": userId,
                "waiguanId": [waiguanId],
                "createTime": datetime.now()
            })
        
        # 检查该外观是否已在关注列表中
        if waiguanId in result['waiguanId']:
            # 如果在列表中，则移除（取消关注）
            return self.db['toggle_like_waiguan'].update_one(
                {"_id": result['_id']},
                {"$pull": {"waiguanId": waiguanId}}
            )
        else:
            # 如果不在列表中，则添加（添加关注）
            return self.db['toggle_like_waiguan'].update_one(
                {"_id": result['_id']},
                {"$push": {"waiguanId": waiguanId}}
            )

    ## 获取用户关注的外观列表
    def getFollowList(self, userId):
        """获取用户关注的外观列表"""
        result = self.db['toggle_like_waiguan'].find_one({"userId": userId})
        if result:
            # 获取关注的外观ID列表
            waiguan_ids = result.get('waiguanId', [])
            # 查询这些外观的详细信息
            waiguan_list = list(self.db['jx3_all_data22'].find(
                {"_id": {"$in": waiguan_ids}}
            ))
            return waiguan_list
        return []

    ## 提交外观别名
    def submitAlias(self, waiguanId: str, userId: str, alias: str):
        ##  先查询提交的别名是否存在
        has_exist = self.db['pending_alias'].find_one({
            "waiguanId": waiguanId,
            "content": alias.strip(),
            "userId": userId
        })
        if has_exist is not None:
            return {
                "code": 500,
                "message": "别名已存在"
            }
            
        ## 根据外观id查询外观    
        waiguan = self.db['jx3_all_data22'].find_one({"_id": ObjectId(waiguanId)})
        if waiguan is not None:
            ## 创建别名对象
            alias_obj = {
                "content": alias.strip(),
                "userId": userId,
                "status": 0,  # 0:未审核 1:已审核
                "createTime": datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            }
            
            ## 添加到pendingAlias数组中
            result = self.db['jx3_all_data22'].update_one(
                {
                    "_id": ObjectId(waiguanId),
                    # 确保不存在相同内容的别名
                    "pendingAlias": {
                        "$not": {
                            "$elemMatch": {
                                "content": alias.strip(),
                                "userId": userId
                            }
                        }
                    }
                }, 
                {"$push": {"pendingAlias": alias_obj}}
            )
            if result.acknowledged:
                ##如果插入成功, 则在待审核col中也插入数据
                alias_obj['waiguanId'] = waiguanId
                self.db['pending_alias'].insert_one(alias_obj)
                return {
                    "code": 200,
                    "message": "提交成功"
                }
            else:
                return {
                    "code": 500,
                    "message": "添加别名到外观表中失败"
                }
        else:
            return None
        
    ## 根据itemId获取外观详情
    def getWaiguanDetailByItemId(self, itemId: int):
        result = self.db['jx3_all_data22'].find_one({"itemBase.item.itemId": itemId})
        if result is not None:
            result['_id'] = result['_id'].__str__()
            return result
        else:
            return None

    ##根据id获取外观详情
    def getWaiguanDetail(self, id: str, userId: str = None):
        result = self.db['jx3_all_data22'].find_one({"_id": ObjectId(id)})
        if result is not None:
            result['_id'] = result['_id'].__str__()
            return result
        else:
            return None
    
    ## 获取外观列表
    def getWaiguanList(self, type: str, keyword: str = None, page: int = 1, limit: int = 10):
        query = {}
        # 构建基础查询条件
        if type != '全部':
            query = {
                "itemBase.typeName": type
            }
        # 如果有关键字，添加模糊查询条件
        if keyword:
            # 确保 keyword 是一个字符串
            keyword_str = keyword if isinstance(keyword, str) else str(keyword)
            query["$or"] = [
                {"itemBase.info": {"$regex": keyword_str, "$options": "i"}},  # i 表示不区分大小写
                # 匹配已审核的别名
                {
                    "$and": [
                        {"pendingAlias": {"$exists": True}},  # pendingAlias字段存在
                        {"pendingAlias": {"$ne": None}},      # pendingAlias不为null
                        {
                            "pendingAlias": {
                                "$elemMatch": {
                                    "content": {"$regex": keyword_str, "$options": "i"},
                                    "status": 1  # 只匹配已审核的别名
                                }
                            }
                        }
                    ]
                }
            ]
        
        # 调试输出
        print(f"Generated query: {query}")
        
        try:
            # 计算总数
            total = self.db['jx3_all_data22'].count_documents(query)
            
            # 执行分页查询
            result = self.db['jx3_all_data22'].find(query).skip((page - 1) * limit).limit(limit)
            
            # 转换结果
            items = []
            for item in result:
                item['_id'] = str(item['_id'])
                items.append(item)
            
            return {
                "items": items,
                "total": total,
                "page": page,
                "limit": limit,
                "hasMore": total > page * limit
            }
            
        except Exception as e:
            print(f"Query error: {str(e)}")
            return None
    
    ## 获取评价结果
    def getEvaluateResult(self, tuanId:str, createrId:str, userId:str):
        result = self.db['evaluate_pintuan'].find({
            "tuanId":tuanId,
            "userId":userId
        })
        if result is not None:
            result_list = []
            for item in result:
                item['_id'] = item['_id'].__str__()
                result_list.append(item)
            return result_list
        else:
            return None

    ## 评价团
    def evaluate_tuan(self, evaluateData:EvaluateModel):
        values = []
        tuan_id  = evaluateData.tuanId
        leader_value = {
            "tuanId":tuan_id,
            "userId":evaluateData.leader["userId"],
            "speed":evaluateData.leader["speed"],
            "credit":evaluateData.leader["credit"],
            "evaluateUserId":evaluateData.evaluateUserId
        }
        values.append(leader_value)
        for member in evaluateData.members if evaluateData.members is not None else []:
            member_value = {
                "tuanId":tuan_id,
                "userId":member["userId"],
                "credit":member["credit"],
                "evaluateUserId":evaluateData.evaluateUserId
            }
            values.append(member_value)
        result = self.db['evaluate_pintuan'].insert_many(values)
        if result.acknowledged:
            ## 更新团评价状态
            return self.db['pintuan'].update_one({"_id": ObjectId(tuan_id)}, {"$set": {"status": 2}})
        else:
            return None


    ## 保存用户
    def save_user(self, user: dict):

        return self.db['user'].insert_one(user)

    ## 根据openId查询用户
    def queryUserByOpenId(self, openId: str):
        result = self.db['user'].find_one(
            {
                "open_id": openId,
                "deleted": False
            }
        )
        return result
    
    ## 根据id查询地址
    def get_address_by_id(self, id:str):
        result = self.db['user_address'].find_one(
            {
                "_id": ObjectId(id),
                "deleted": False
            }
        )
        return result

    ## 删除用户地址
    def delete_address(self, addressId: str):
        print(addressId)
        return self.db['user_address'].update_one(
            {"_id": ObjectId(addressId)}, {"$set": {"deleted": True}})

    ## 保存或更新用户地址
    def save_or_update_address(self, address: AddressSaveModel):
        ### 将用户地址数据保存到MongoDB中, db是jx3_pintuan ,collection为user_address
        if address.id is not None:
            ## 根据 _id 更新
            return self.db['user_address'].update_one({
                "_id": ObjectId(address.id)
            },
                {
                "$set": {
                    "user_id": address.user_id if address.user_id is not None else "",
                    "name": address.name,
                    "faction": address.faction,
                    "server": address.server,
                    "contactType": address.contactType,
                    "contact": address.contact,
                    "signature": address.signature,
                    "updateTime": address.updateTime if address.updateTime is not None else datetime.now().strftime(
                        '%Y-%m-%d %H:%M:%S'),
                }
            }
            )
        else:
            return self.db['user_address'].insert_one({
                "user_id": address.user_id if address.user_id is not None else "",
                "name": address.name,
                "faction": address.faction,
                "server": address.server,
                "contactType": address.contactType,
                "contact": address.contact,
                "signature": address.signature,
                "createTime": address.createTime if address.createTime is not None else datetime.now().strftime(
                    '%Y-%m-%d %H:%M:%S'),
                "updateTime": address.updateTime if address.updateTime is not None else datetime.now().strftime(
                    '%Y-%m-%d %H:%M:%S'),
                "deleted": False
            })

    ## 根据用户id获取用户地址
    def get_address_by_user_id(self, user_id):
        ## 使用find方法根据 user_id 和 deleted 查询, 并且进行分页, 将结果封装为列表
        result = self.db['user_address'].find({
            "user_id": user_id,
            "deleted": False
        }).skip(0).limit(10)
        print(result)
        if result is not None:
            list = []
            for doc in result:
                doc['_id'] = doc['_id'].__str__()
                list.append(doc)
            return list
        else:
            return None

    ## 获取热度前十的团
    def get_hot10(self):
        ### 从昨天的0点到今天的0点, 查询followed_num字段的值,并且进行排序,并且进行分页
        yesterday = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0) - timedelta(days=1)
        today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)

        query = {
            "updateTime": {
                "$gte": yesterday.strftime('%Y-%m-%d %H:%M:%S'),
                "$lt": today.strftime('%Y-%m-%d %H:%M:%S')
            },
            "followed_num": {
                "$exists": True,
                "$type": "int"
            }
        }
        sort = [
            ("followed_num", pymongo.DESCENDING),
        ]
        print(query)
        result = self.db['jx3_trade_data'].find(query).sort(sort).skip(0).limit(10)
        # result = self.db['jx3_trade_data'].find(query).limit(10)
        return result
    
    ## 更新团
    def update_tuan(self, pintuan: TuanCreateModel):
        return self.db['pintuan'].update_one({"_id": ObjectId(pintuan.id)}, {"$set": pintuan.model_dump()})

    ## 创建团

    def create_tuan(self, pintuan: TuanCreateModel):
        pintuan.createTime = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        pintuan.updateTime = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        pintuan.deleted = False
        return self.db['pintuan'].insert_one(pintuan.model_dump())

    ## 获取团列表
    def getPintuanList(self,type:str,userId:str,page:int,limit:int):
        query={}
        if userId is None:
            query = {
                "status": {
                    "$in":[int(x) for x in type.split(',')]
                }
            }
        else:
            query = {
                "status": {
                    "$in": [int(x) for x in type.split(',')]
                },
                "gifts":{
                    "$elemMatch":{
                        "userId":userId
                    }
                }
            }
        
        result = self.db['pintuan'].find(query).skip((page-1)*limit).limit(limit)
        ## 过滤掉createTime + duration 小于当前时间的数据, 并把他的status字段设置为2
        result_list = []
        for item in result:
            create_time_str  = item['createTime']
            duration = int(item['duration'])

            create_time =datetime.strptime(create_time_str,'%Y-%m-%d %H:%M:%S')
            end_time = create_time + timedelta(days=duration)
            if datetime.now() > end_time:
                self.db['pintuan'].update_one({"_id": item['_id']}, {"$set": {"status": 2}})
                continue
            else:
                item['_id'] = item['_id'].__str__()
                for gift in item['gifts']:
                    userinfo = self.queryUserByOpenId(gift['userId'])
                    if userinfo is not None:
                        gift['avatar'] = userinfo['avatar_url']
                        gift['userName'] = userinfo['nickname']
                result_list.append(item)

        return result_list

    ## 获取团详情
    def getPintuanDetail(self, tuanId):
        query = {
            "_id": ObjectId(tuanId)
        }
        result = self.db['pintuan'].find_one(query)
        result['_id'] = result['_id'].__str__()
        return result

    def removeDuplicates(self):
        try:
            # 1. 找出重复的 itemId
            pipeline = [
                {
                    "$group": {
                        "_id": "$itemBase.item.itemId",
                        "count": {"$sum": 1},
                        "docs": {"$push": "$_id"}
                    }
                },
                {
                    "$match": {
                        "count": {"$gt": 1}
                    }
                }
            ]
            
            duplicates = list(self.db['jx3_all_data'].aggregate(pipeline))
            
            # 2. 对每组重复数据，保留第一条，删除其他的
            for group in duplicates:
                # 跳过第一个文档（保留），删除其余的
                docs_to_delete = group['docs'][1:]
                
                # 删除重复文档
                self.db['jx3_all_data'].delete_many({
                    "_id": {"$in": docs_to_delete}
                })
                
            return {"message": f"Successfully removed duplicates", "processed_groups": len(duplicates)}
            
        except Exception as e:
            print(f"Error removing duplicates: {str(e)}")
            return None

    def increment_update_items(self):
        try:
            # 获取上次处理的最后一个 _id
            last_id = self.redis_client.get('last_processed_id')
            last_id = ObjectId(last_id.decode()) if last_id else ObjectId('000000000000000000000000')
            
            while True:
                # 分批获取数据
                items = self._get_batch_items(last_id)
                if not items:
                    break
                    
                # 计算每个线程处理的数据量
                chunk_size = ceil(len(items) / self.MAX_WORKERS)
                
                # 将数据分成多个块
                chunks = [items[i:i + chunk_size] for i in range(0, len(items), chunk_size)]
                
                # 使用线程池并行处理数据块
                with ThreadPoolExecutor(max_workers=self.MAX_WORKERS) as executor:
                    futures = []
                    for chunk in chunks:
                        futures.append(executor.submit(self._process_chunk, chunk))
                    
                    # 等待所有任务完成
                    for future in futures:
                        future.result()
                
                # 更新最后处理的 ID
                last_id = items[-1]['_id']
                self.redis_client.set('last_processed_id', str(last_id))
                
                logging.info(f"Processed batch until ID: {last_id}")
                
        except Exception as e:
            logging.error(f"增量更新失败: {str(e)}")
            return None
            
    def _get_batch_items(self, last_id):
        """获取一批待处理的数据"""
        pipeline = [
            {
                "$match": {
                    "_id": {"$gt": last_id}
                }
            },
            {
                "$limit": self.BATCH_SIZE
            }
        ]
        
        return list(self.db['jx3_trade_data'].aggregate(pipeline))
        
    def _process_chunk(self, items):
        """批量处理一组商品数据"""
        try:
            # 1. 收集所有商品ID
            item_ids = [item['attrs']['item_index'] for item in items]
            
            # 2. 批量查询已存在的商品
            existing_items = set(doc['itemBase']['item']['itemId'] 
                for doc in self.db['jx3_all_data22'].find(
                    {"itemBase.item.itemId": {"$in": item_ids}},
                    {"itemBase.item.itemId": 1}
                )
            )
            
            # 3. 构建待插入的新商品列表
            new_items = []
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            for item in items:
                if item['attrs']['item_index'] not in existing_items:
                    new_items.append({
                        "itemBase": {
                            "icon": item['thumb'],
                            "item": {
                                "itemId": item['attrs']['item_index'],
                                "itemType": item['attrs']['item_type']
                            },
                            "typeName": item['attrs']['appearance_type_name'],
                            "info": item['info']
                        },
                        "createTime": current_time,
                        "updateTime": current_time,
                        "deleted": False
                    })
            
            # 4. 批量插入新商品
            if new_items:
                self.db['jx3_all_data22'].insert_many(new_items)
                logging.info(f"Batch inserted {len(new_items)} new items")
                
        except Exception as e:
            logging.error(f"批量处理商品失败: {str(e)}")

    def get_increment_status(self):
        """获取增量更新状态"""
        try:
            last_id = self.redis_client.get('last_processed_id')
            if not last_id:
                return {"status": "未开始增量更新"}
                
            last_id = ObjectId(last_id.decode())
            
            # 获取总数据量和已处理数量
            total = self.db['jx3_trade_data'].count_documents({})
            processed = self.db['jx3_trade_data'].count_documents({"_id": {"$lte": last_id}})
            
            return {
                "status": "进行中" if processed < total else "已完成",
                "total": total,
                "processed": processed,
                "progress": f"{(processed/total*100):.2f}%"
            }
            
        except Exception as e:
            logging.error(f"获取状态失败: {str(e)}")
            return None

    
